A database is an integrated collection of data records, files, and other database objects needed by an application. Database objects are stored in database tables that are accessible to a user. A database management system (“DBMS”) is the software that controls the creation, maintenance, and the use of a database. The DBMS handles user queries for data.
A relational database is a database that is perceived by its users as a collection of tables. Each table arranges items and attributes of the items in rows and columns, respectively. Each table row corresponds to an item, and each table column corresponds to an attribute of the item (referred to as a field or, more correctly, as an attribute type or field type).
Database management systems provide mechanisms that permit users to constrain the value of one database entity based on the value or existence of another database entity. One common constraint type is the referential constraint. Referential constraints require that a value referred to by one database entity (e.g., a row in a first table) is associated with an existing entity in the database (e.g., another row in the same or different table). In the context of the Structured Query Language (“SQL”), referential constraints are implemented through the use of Foreign Keys, wherein a database entity's Foreign Key value must equate to the Primary Key value of another, existing, database entity.
A Primary Key for a table is a simple or composite attribute that uniquely identifies rows of the table. A key must be inherently unique and not merely unique at a particular point in time. It is possible, but not typical, to have a table where the only unique identifier is the composite attribute consisting of all the attributes of the table. It is also possible, but not typical, for a table to have more than one unique identifier. In such a case, the table may be said to have multiple candidate keys, one of which could be chosen and designated as the Primary Key; the remaining candidates then would be said to be “alternate keys”. Collectively, primary and alternate keys may be referred to as “unique” keys. Primary and alternate keys must satisfy two time independent properties. First, at no time may two rows of the table have the same value for the key. And second, if the key is composite, then no component of the key can be eliminated without destroying the uniqueness property.
A Foreign Key can be a composite attribute of a table having values required to match those of the Primary Key of some table, which is typically but not necessarily distinct from the table for which the Foreign Key is defined. A Foreign Key value represents a reference to the row containing the matching Primary Key value, which may be called the referenced row or the target row.
A “referential integrity” rule requires a database system to maintain “referential integrity” between Foreign Keys and Primary Keys. The rule states that a database must not contain any unmatched Foreign Key values. An unmatched Foreign Key value is a non-null value for which there does not exist a matching value of the Primary Key in the relevant target table. Note that referential integrity requires Foreign Keys to match Primary Keys. A database system is not required to maintain integrity with respect to alternate keys.
A data warehouse (DW) is a database used for reporting and analysis. The data stored in the warehouse is uploaded from source operational systems (systems that process the day-to-day transactions of an organization). Normally a DW has a data model that describes its information. The data model may have defined referential constraints between tables which are not implemented in the physical build of the database. The referential constraints are not implemented to allow for the loading of data from multiple source databases and to increase the speed of loading. By not building referential constraints between tables, source data can be loaded which does not adhere to the data model constraints.